import pyodbc
import pandas as pd
from log import *

def get_connection():
    """
    建立达梦数据库连接
    :return: connection object
    """
    try:
        conn = pyodbc.connect(
            "DRIVER={DM8 ODBC DRIVER};"
            "SERVER=10.201.158.169;"    # 改成你的数据库地址
            "PORT=5236;"                # 达梦默认端口
            "UID=SYSDBA;"               # 用户名
            "PWD=Aa2676615043;"         # 密码
        )
        return conn
    except Exception as e:
        log_exception(e)
        raise

def get_dm_table_names():
    """
    查询当前用户下的所有表名（达梦）
    :return: list of table names
    """
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute("SELECT TABLE_NAME FROM USER_TABLES")
        tables = cursor.fetchall()

        cursor.close()
        conn.close()

        return [row[0] for row in tables]

    except Exception as e:
        log_exception(e)
        raise

def search_dm_sql_to_df(table_name, select_condition='*'):
    """
    从达梦数据库查询数据并转为 DataFrame
    :param table_name: 表名
    :param select_condition: 选择字段（默认 *）
    :return: pd.DataFrame
    """
    try:
        conn = get_connection()
        cursor = conn.cursor()

        # 查询数据
        cursor.execute(f"SELECT {select_condition} FROM {table_name}")
        data = cursor.fetchall()

        # 获取列名
        cursor.execute(f"SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('{table_name}')")
        columns = cursor.fetchall()
        column_names = [col[0] for col in columns]

        df = pd.DataFrame(data, columns=column_names)

        cursor.close()
        conn.close()

        return df

    except Exception as e:
        log_exception(e)
        raise

def delete_dm_record(table_name, pri_key, key_content):
    """
    删除达梦数据库中指定记录（根据主键）
    :param table_name: 表名（建议大写）
    :param pri_key: 主键字段名
    :param key_content: 要删除的主键值
    """
    try:
        conn = get_connection()
        cursor = conn.cursor()

        sql = f"DELETE FROM {table_name} WHERE {pri_key} = ?"
        cursor.execute(sql, (key_content,))

        cursor.commit()
        print(f"已从达梦数据库的 {table_name} 表中删除 {pri_key} = {key_content} 的记录。")

        cursor.close()
        conn.close()
    except Exception as e:
        log_exception(e)
        raise

def update_dm_record(table_name, pri_key, key_content, update_key, update_content):
    """
    更新达梦数据库中指定记录的字段值
    """
    try:
        if not update_key or not update_content or len(update_key) != len(update_content):
            raise ValueError("字段名和内容列表必须长度一致且非空")

        conn = get_connection()
        cursor = conn.cursor()

        # 确保表名和字段名都加上双引号以适应达梦数据库大小写敏感的特性
        quoted_table = f'{table_name}'  # 表名加双引号
        quoted_pri_key = f'{pri_key}'  # 主键字段加双引号
        set_clause = ", ".join([f'{key} = ?' for key in update_key])  # 字段名加双引号
        print(set_clause)
        sql = f'UPDATE {quoted_table} SET {set_clause} WHERE {quoted_pri_key} = ?;'

        params = update_content + [key_content]

        print(f"执行SQL: {sql}")
        print(f"参数: {params}")

        cursor.execute(sql, params)
        cursor.commit()
        affected = cursor.rowcount

        if affected == 0:
            print("未匹配到记录，未更新。")
        else:
            print(f"成功更新 {affected} 条记录。")

        cursor.close()
        conn.close()
    except Exception as e:
        log_exception(e)
        raise

# 示例运行
if __name__ == '__main__':
    table_name = 'TEST'  # 注意表名要大写或确保数据库里是大写
    #df = search_dm_sql_to_df(table_name)
    #print(df)
    #update_dm_record("sync", "ID", "000002", ["name"], ["update"])
    delete_dm_record("SYNC", "ID", "000002")
